import openpyxl
import os
from datetime import datetime

# 设置文件路径
base_dir = "e:/Projects/program-py/LEARN-数据分析/CASE-Excel_merge"
employee_info_file = f"{base_dir}/员工基本信息表.xlsx"
performance_file = f"{base_dir}/员工绩效表.xlsx"
output_file = f"{base_dir}/员工信息_含绩效表.xlsx"

def read_employee_info():
    """读取员工基本信息表"""
    try:
        workbook = openpyxl.load_workbook(employee_info_file, data_only=True)
        sheet = workbook.active
        
        # 读取所有数据
        data = []
        headers = None
        for i, row in enumerate(sheet.iter_rows(values_only=True)):
            if i == 0:  # 表头
                headers = list(row)
            else:
                data.append(list(row))
        
        workbook.close()
        return headers, data
    except Exception as e:
        print(f"读取员工基本信息表时出错: {e}")
        return None, None

def read_performance_2024_q4():
    """读取2024年第4季度的绩效数据"""
    try:
        workbook = openpyxl.load_workbook(performance_file, data_only=True)
        sheet = workbook.active
        
        # 读取所有数据
        data = []
        headers = None
        for i, row in enumerate(sheet.iter_rows(values_only=True)):
            if i == 0:  # 表头
                headers = list(row)
            else:
                # 筛选2024年第4季度的数据
                if len(row) >= 4 and str(row[1]) == '2024' and str(row[2]) == '4':
                    data.append(list(row))
        
        workbook.close()
        
        # 创建员工ID到绩效评分的映射
        performance_map = {}
        for row in data:
            employee_id = row[0]
            performance_score = row[3]
            performance_map[employee_id] = performance_score
        
        return performance_map
    except Exception as e:
        print(f"读取员工绩效表时出错: {e}")
        return {}

def merge_employee_data():
    """合并员工信息和绩效数据"""
    # 读取员工基本信息
    headers, employee_data = read_employee_info()
    if headers is None or employee_data is None:
        return False
    
    # 读取2024年第4季度绩效数据
    performance_map = read_performance_2024_q4()
    
    # 添加新的表头
    new_headers = headers + ['2024年第4季度绩效评分']
    
    # 合并数据
    merged_data = []
    for employee in employee_data:
        employee_id = employee[0]
        performance_score = performance_map.get(employee_id, '无数据')
        merged_row = employee + [performance_score]
        merged_data.append(merged_row)
    
    # 保存合并后的数据
    save_merged_data(new_headers, merged_data)
    
    # 显示统计信息
    total_employees = len(employee_data)
    employees_with_performance = len([emp for emp in merged_data if emp[-1] != '无数据'])
    
    print(f"\n合并统计:")
    print(f"总员工数: {total_employees}")
    print(f"有2024年第4季度绩效数据的员工数: {employees_with_performance}")
    print(f"无绩效数据的员工数: {total_employees - employees_with_performance}")
    
    return True

def save_merged_data(headers, data):
    """保存合并后的数据到新的Excel文件"""
    try:
        # 创建新的工作簿
        workbook = openpyxl.Workbook()
        sheet = workbook.active
        sheet.title = "员工信息_含绩效"
        
        # 写入表头
        sheet.append(headers)
        
        # 写入数据
        for row in data:
            sheet.append(row)
        
        # 设置列宽
        for col in range(1, len(headers) + 1):
            sheet.column_dimensions[openpyxl.utils.get_column_letter(col)].width = 15
        
        # 保存文件
        workbook.save(output_file)
        workbook.close()
        
        print(f"\n合并后的文件已保存到: {output_file}")
        
        # 显示前5行数据
        print("\n合并后的数据预览 (前5行):")
        print("-" * 80)
        
        # 格式化输出
        max_widths = []
        for col in range(len(headers)):
            max_width = max(len(str(headers[col])), 
                          max(len(str(row[col])) if row[col] is not None else 0 for row in data[:5]))
            max_widths.append(max(max_width, 12))
        
        # 打印表头
        header_str = ""
        for i, header in enumerate(headers):
            header_str += f"{str(header):<{max_widths[i]}}  "
        print(header_str)
        print("-" * 80)
        
        # 打印前5行数据
        for row in data[:5]:
            row_str = ""
            for i, cell in enumerate(row):
                cell_value = str(cell) if cell is not None else ""
                row_str += f"{cell_value:<{max_widths[i]}}  "
            print(row_str)
        
    except Exception as e:
        print(f"保存合并数据时出错: {e}")

def main():
    print("=" * 80)
    print("合并员工基本信息表和绩效表")
    print("=" * 80)
    
    success = merge_employee_data()
    
    if success:
        print("\n" + "=" * 80)
        print("合并完成!")
    else:
        print("\n" + "=" * 80)
        print("合并失败!")

if __name__ == "__main__":
    main()